package com.fau.fm.service.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.validation.ConstraintViolation;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.struts2.ServletActionContext;
import org.hibernate.Hibernate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import com.fau.fm.entity.MedicalAdmin;
import com.fau.fm.entity.MedicalKeyValue;
import com.fau.fm.entity.MedicalLeaveApprove;
import com.fau.fm.entity.MedicalLeaveDeduct;
import com.fau.fm.entity.MedicalMedicine;
import com.fau.fm.entity.MedicalPerformanceSalary;
import com.fau.fm.entity.MedicalStaffBasic;
import com.fau.fm.entity.MedicalStaffSalary;
import com.fau.fm.service.IPerformanceSalManagerService;
import com.fau.fm.util.DateUtil;
import com.fau.fm.util.Page;
import com.fau.fm.util.ParamUtil;
import com.fau.fm.util.SystemConfig;
import com.fau.fm.util.ValidatorUtil;

@Service
@Transactional(readOnly = true)
public class PerformanceSalManagerService extends BaseService implements IPerformanceSalManagerService {

	@Override
	public Page findCallEmployeeByPage(Map<String, Object> map, MedicalPerformanceSalary performance, MedicalAdmin admin) {
		StringBuilder statusSql = new StringBuilder("from MedicalPerformanceSalary m where 1=1 and m.eid = ? and m.deptType = ? and m.isLeader = ? ");
		StringBuilder countSql = new StringBuilder("select count(*) from MedicalPerformanceSalary m where 1=1 and m.eid = ? and m.deptType = ? and m.isLeader = ? ");
		List<Object> objectList = new ArrayList<Object>();
		objectList.add(admin.getEnterpriseId());
		objectList.add(SystemConfig.CM_SYSTEM_TYPE);
		objectList.add(SystemConfig.GROUP_EMPLOYEE);
		
		//排序
		String[] sortField = (String[]) map.get("sortField");
		String[] sortOrder = (String[]) map.get("sortOrder");
		if(StringUtils.isNotBlank(sortField[0]) && StringUtils.isNotBlank(sortOrder[0])) {
			statusSql.append("order by m." + sortField[0] + " " + sortOrder[0]); 
		} else {
			statusSql.append(" order by m.createTime desc ");
		}
		
		Page page = this.iBaseDao.getPage(ParamUtil.getInteger(map,"pageIndex",1),ParamUtil.getInteger(map, "pageSize", 20), map, countSql.toString(), statusSql.toString(), objectList);
		List<MedicalPerformanceSalary> list = new ArrayList<MedicalPerformanceSalary>();
		for(Object obj : page.getResult()){
			MedicalPerformanceSalary salary = (MedicalPerformanceSalary) obj;
			if(StringUtils.isNotBlank(salary.getMedicineId())){
				MedicalMedicine sort = (MedicalMedicine) this.iBaseDao.getObjectByHql("from MedicalMedicine m where m.id = ? ", salary.getMedicineId());
				salary.setMedicineName(sort.getName());
			}
			list.add(salary);
		}
		page.setResult(list);
		return page;
	}

	@Override
	public Page findNuclearEmployeeByPage(Map<String, Object> map, MedicalPerformanceSalary performance, MedicalAdmin admin) {
		StringBuilder statusSql = new StringBuilder("from MedicalPerformanceSalary m where 1=1 and m.eid = ? and m.deptType = ? and m.isLeader = ? ");
		StringBuilder countSql = new StringBuilder("select count(*) from MedicalPerformanceSalary m where 1=1 and m.eid = ? and m.deptType = ? and m.isLeader = ? ");
		List<Object> objectList = new ArrayList<Object>();
		objectList.add(admin.getEnterpriseId());
		objectList.add(SystemConfig.OM_SYSTEM_TYPE);
		objectList.add(SystemConfig.GROUP_EMPLOYEE);
		
		Page page = this.iBaseDao.getPage(ParamUtil.getInteger(map,"pageIndex",1),ParamUtil.getInteger(map, "pageSize", 20), map, countSql.toString(), statusSql.toString(), objectList);
		return page;
	}

	@Override
	public Page findGroupByPage(Map<String, Object> map, MedicalPerformanceSalary performance, MedicalAdmin admin) {
		StringBuilder statusSql = new StringBuilder("from MedicalPerformanceSalary m where 1=1 and m.eid = ? and m.isLeader = ? ");
		StringBuilder countSql = new StringBuilder("select count(*) from MedicalPerformanceSalary m where 1=1 and m.eid = ? and m.isLeader = ? ");
		List<Object> objectList = new ArrayList<Object>();
		objectList.add(admin.getEnterpriseId());
		objectList.add(SystemConfig.GROUP_LEADER);
		
		Page page = this.iBaseDao.getPage(ParamUtil.getInteger(map,"pageIndex",1),ParamUtil.getInteger(map, "pageSize", 20), map, countSql.toString(), statusSql.toString(), objectList);
		List<MedicalPerformanceSalary> list = new ArrayList<MedicalPerformanceSalary>();
		for(Object obj : page.getResult()){
			MedicalPerformanceSalary salary = (MedicalPerformanceSalary) obj;
			MedicalKeyValue keyValue = (MedicalKeyValue) this.iBaseDao.getObjectByHql("from MedicalKeyValue m where m.hospitalId = ? and m.keyName = ? ", "0", salary.getDeptType());
			if(null != keyValue) {
				salary.setDeptTypeName(keyValue.getKeyValue());
			}
			list.add(salary);
		}
		page.setResult(list);
		return page;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MedicalMedicine> findMedicineSortList(MedicalAdmin admin) {
		return this.iBaseDao.getBySql("from MedicalMedicine a where a.hospitalId=? ", null, admin.getEnterpriseId());
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public boolean save(MedicalPerformanceSalary performance) {
		if(StringUtils.isNotBlank(performance.getMedicineId())) {
			// 话务组员
			performance.setDeptType(SystemConfig.CM_SYSTEM_TYPE);
			performance.setIsLeader(SystemConfig.GROUP_EMPLOYEE);
		} else if(StringUtils.isBlank(performance.getDeptType())) {
			// 核单组员
			performance.setDeptType(SystemConfig.OM_SYSTEM_TYPE);
			performance.setIsLeader(SystemConfig.GROUP_EMPLOYEE);
		} else {}
		
		Set<ConstraintViolation<Object>> errorInfo = ValidatorUtil.validateObj(performance);
		if(errorInfo.size() > 0) {
			return false;
		} else {
			return this.iBaseDao.saveObject(performance);
		}
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public boolean saveEdit(MedicalPerformanceSalary performance) {
		MedicalPerformanceSalary salary = (MedicalPerformanceSalary) this.iBaseDao.getObject(MedicalPerformanceSalary.class, performance.getId());
		salary.setNumber(performance.getNumber());
		salary.setMoney(performance.getMoney());
		if(StringUtils.isNotBlank(performance.getMedicineId())){
			salary.setMedicineId(performance.getMedicineId());
		}
		salary.setUpdateTime(new Date());
		Set<ConstraintViolation<Object>> errorInfo = ValidatorUtil.validateObj(salary);
		if(errorInfo.size() > 0) {
			return false;
		} else {
			return this.iBaseDao.saveObject(salary);
		}
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public void delete(MedicalPerformanceSalary proformance) {
		String[] ids = proformance.getId().split(",");
		if(1 == ids.length) {
			this.iBaseDao.deleteObject(proformance);
		} else {
			for(String id : ids) {
				this.iBaseDao.updateHql("delete from MedicalPerformanceSalary m where m.id = ? ", id);
			}
		}
	}

	@Override
	public boolean includeMedicine(MedicalPerformanceSalary performance, MedicalAdmin admin) {
		boolean flag = false;
		int count = 0;
		if(StringUtils.isNotBlank(performance.getMedicineId())) {
			count = this.iBaseDao.getIntCount("select count(*) from MedicalPerformanceSalary m where m.eid = ? and m.medicineId = ? ", admin.getEnterpriseId(), performance.getMedicineId());
		} else if(StringUtils.isNotBlank(performance.getDeptType()) && StringUtils.isNotBlank(performance.getIsLeader())){
			count = this.iBaseDao.getIntCount("select count(*) from MedicalPerformanceSalary m where m.eid = ? and m.deptType = ? and m.isLeader = ? ", admin.getEnterpriseId(), performance.getDeptType(), performance.getIsLeader());
		}
		if(count > 0) {
			flag = true;
		} else {}
		return flag;
	}
	
	@Override
	public Page findAllStaffSalary(Map<String, Object> map, MedicalPerformanceSalary salary) throws ParseException {
		StringBuilder statusSql = new StringBuilder("from MedicalStaffSalary m where 1=1 and m.hospitalId = ? ");
		StringBuilder countSql = new StringBuilder("select count(*) from MedicalStaffSalary m where 1=1 and m.hospitalId = ? ");
		List<Object> objectList = new ArrayList<Object>();
		objectList.add(salary.getHospitalId());
		
		if(StringUtils.isNotBlank(salary.getStime()) && StringUtils.isNotBlank(salary.getEtime())) {
			Date ds = DateUtils.parseDate(salary.getStime(), "yyyy-MM-dd");
			Date de = DateUtils.parseDate(salary.getEtime(), "yyyy-MM-dd");
			statusSql.append("and m.calcStime >= ? and m.calcEtime <= ? ");
			countSql.append("and m.calcStime >= ? and m.calcEtime <= ? ");
			objectList.add(ds);
			objectList.add(de);
		} else {
			statusSql.append("and m.calcStime >= ? and m.calcEtime <= ? ");
			countSql.append("and m.calcStime >= ? and m.calcEtime <= ? ");
			objectList.add(DateUtil.getDate(-31));
			objectList.add(new Date());
		}
		
		//排序
		String[] sortField = (String[]) map.get("sortField");
		String[] sortOrder = (String[]) map.get("sortOrder");
		if(StringUtils.isNotBlank(sortField[0]) && StringUtils.isNotBlank(sortOrder[0])) {
			statusSql.append("order by m." + sortField[0] + " " + sortOrder[0]); 
		} else {
			statusSql.append(" order by m.calcStime desc ");
		}
		
		Page page = this.iBaseDao.getPage(ParamUtil.getInteger(map,"pageIndex",1),ParamUtil.getInteger(map, "pageSize", 20), map, countSql.toString(), statusSql.toString(), objectList);
		List<MedicalStaffSalary> list = new ArrayList<MedicalStaffSalary>();
		for(Object obj : page.getResult()) {
			MedicalStaffSalary bean = (MedicalStaffSalary) obj;
			MedicalKeyValue keyVal = this.iBaseDao.getValueByKey(bean.getStaffBasic().getUser().getEnterpriseId(), SystemConfig.HM_SYSTEM_TYPE, bean.getStaffBasic().getDeptType());
			if(null != keyVal) {
				bean.getStaffBasic().setDept(keyVal.getKeyValue());
			}
			MedicalAdmin user = bean.getStaffBasic().getUser();
			Hibernate.initialize(user.getGroup());
			list.add(bean);
		}
		return page;
	}

	@SuppressWarnings("unchecked")
	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public boolean saveCalculatingSalaries(MedicalPerformanceSalary salary) {
		
		Date startTime = salary.getStartTime();
		Date endTime = salary.getEndTime();
		
		// 话务员
		List<MedicalStaffBasic> callEmployeelist = this.iBaseDao.getBySql("from MedicalStaffBasic m where m.user.enterpriseId = ? "
				+ "and m.deptType = ? and m.isLeader = ? ", null, salary.getHospitalId(), SystemConfig.CM_SYSTEM_TYPE, SystemConfig.GROUP_EMPLOYEE);
		
		for(MedicalStaffBasic callE : callEmployeelist) {
			MedicalStaffSalary bean = new MedicalStaffSalary();
			bean.setCalcStime(startTime);
			bean.setCalcEtime(endTime);
			bean.setStaffBasic(callE);
			
			// 查询话务员的金额
			Object object = this.iBaseDao.getObjectByHql("select m.productId, count(m.productId) from MedicalProductStorage m where m.userId = ?"
					+ " and m.status = ? and m.createTime between ? and ? group by m.productId ", callE.getUser().getId(), SystemConfig.HAS_SECTIONED, 
					startTime, endTime);
			BigDecimal total = new BigDecimal(0);	// 总金额
			if(null != object) {
				List<Object> objList = (List<Object>) object;
				for(Object o : objList) {
					Object[] obj = (Object[]) o;
					if(null != obj[0] && null != obj[1]) {
						// 绩效薪资
						MedicalPerformanceSalary performanceSalary = (MedicalPerformanceSalary) this.iBaseDao.getObjectByHql("from MedicalPerformanceSalary m "
								+ " where m.medicineId = ? ", obj[0].toString());
						// 单个产品的提成金额
						double sinProduct = performanceSalary.getMoney().doubleValue() * Integer.parseInt(obj[1].toString());
						total = total.add(new BigDecimal(sinProduct));
					}
				}
			}
			
			// 话务员的绩效薪资(话务员的业绩（总金额）*提成系数（百分比）)
			bean.setEffectSalary(total);	// 话务员绩效薪资
			
			int count = this.iBaseDao.getIntCount("select count(*) from MedicalLeaveApprove m where m.user.id = ? and m.createTime between ? and ? "
					, callE.getUser().getId(), startTime, endTime);
			BigDecimal leave = new BigDecimal(0);
			if(count > 0){
				List<MedicalLeaveApprove> appList = this.iBaseDao.getBySql("from MedicalLeaveApprove m where m.user.id = ? and m.createTime between ? and ? ", 
						null, callE.getUser().getId(), startTime, endTime);
				for(MedicalLeaveApprove app : appList) {
					MedicalLeaveDeduct deduct = (MedicalLeaveDeduct) this.iBaseDao.getObjectByHql("from MedicalLeaveDeduct m where m.leaveType = ? ", app.getLeaveType());
					if(null != deduct) {
						leave = leave.add(deduct.getDeduct().multiply(new BigDecimal(app.getDays())));
					}
				}
			}
			bean.setLeaveDeduct(leave);
			// 实发工资
			BigDecimal practicalSalary = new BigDecimal(0);
			// 计算话务员薪资(工资 = 基本工资+提成工资-其他扣款工资)
			practicalSalary = practicalSalary.add(bean.getStaffBasic().getBasic()).add(bean.getEffectSalary()).subtract(leave);
			bean.setPracticalSalary(practicalSalary);
			bean.setHospitalId(salary.getHospitalId());
			Set<ConstraintViolation<Object>> errorInfo = ValidatorUtil.validateObj(bean);
			if(errorInfo.size() > 0) {
				return false;
			} else {
				this.iBaseDao.saveObject(bean);
			}
		}
		 
		// 话务组长
		List<MedicalStaffBasic> callGrouplist = this.iBaseDao.getBySql("from MedicalStaffBasic m where m.user.enterpriseId = ? "
				+ "and m.deptType = ? and m.isLeader = ? ", null, salary.getHospitalId(), SystemConfig.CM_SYSTEM_TYPE, SystemConfig.GROUP_LEADER);
		
		for(MedicalStaffBasic callG : callGrouplist) {
			MedicalStaffSalary bean = new MedicalStaffSalary();
			bean.setCalcStime(startTime);
			bean.setCalcEtime(endTime);
			bean.setStaffBasic(callG);
			
			// 查询本话务组完成的总金额
			Object object = this.iBaseDao.getObjectByHql("select sum(m.payment), sum(m.freight) from MedicalProductStorage m where m.groupId = ?"
					+ " and m.status = ? and m.createTime between ? and ? ", callG.getUser().getGroup().getId(), SystemConfig.HAS_SECTIONED,
					startTime, endTime);
			BigDecimal total = new BigDecimal(0);	// 总金额
			if(null != object) {
				Object[] obj = (Object[]) object;
				if(null != obj[0]) {
					total = total.add(new BigDecimal(obj[0].toString()));
				}
				if(null != obj[1]) {
					total = total.add(new BigDecimal(obj[1].toString()));
				}
			}
			
			// 绩效薪资
			MedicalPerformanceSalary performanceSalary = (MedicalPerformanceSalary) this.iBaseDao.getObjectByHql("from MedicalPerformanceSalary m "
					+ " where m.deptType = ? and m.isLeader = ? ", callG.getDeptType(), callG.getIsLeader());
			
			// 话务组长的绩效薪资(话务组的业绩（总金额）*提成系数（百分比）)
			if(null != performanceSalary){
				double amount = total.multiply(performanceSalary.getMoney()).doubleValue()/100;
				bean.setEffectSalary(new BigDecimal(amount));	// 话务组长绩效薪资
			}else{
				bean.setEffectSalary(new BigDecimal("0"));
			}
			
			int count = this.iBaseDao.getIntCount("select count(*) from MedicalLeaveApprove m where m.user.id = ? and m.createTime between ? and ? "
					, callG.getUser().getId(), startTime, endTime);
			BigDecimal leave = new BigDecimal(0);
			if(count > 0){
				List<MedicalLeaveApprove> appList = this.iBaseDao.getBySql("from MedicalLeaveApprove m where m.user.id = ? and m.createTime between ? and ? ", 
						null, callG.getUser().getId(), startTime, endTime);
				for(MedicalLeaveApprove app : appList) {
					MedicalLeaveDeduct deduct = (MedicalLeaveDeduct) this.iBaseDao.getObjectByHql("from MedicalLeaveDeduct m where m.leaveType = ? ", app.getLeaveType());
					if(null != deduct) {
						leave = leave.add(deduct.getDeduct().multiply(new BigDecimal(app.getDays())));
					}
				}
			}
			bean.setLeaveDeduct(leave);
			// 实发工资
			BigDecimal practicalSalary = new BigDecimal(0);	
			// 计算话务组长薪资(工资 = 基本工资+提成工资-其他扣款工资)
			practicalSalary = practicalSalary.add(bean.getStaffBasic().getBasic()).add(bean.getEffectSalary()).subtract(leave);
			
			bean.setPracticalSalary(practicalSalary);
			bean.setHospitalId(salary.getHospitalId());
			Set<ConstraintViolation<Object>> errorInfo = ValidatorUtil.validateObj(bean);
			if(errorInfo.size() > 0) {
				return false;
			} else {
				this.iBaseDao.saveObject(bean);
			}
		}
		
		// 核单员
		List<MedicalStaffBasic> nuclearEmployeelist = this.iBaseDao.getBySql("from MedicalStaffBasic m where m.user.enterpriseId = ? "
				+ "and m.deptType = ? and m.isLeader = ? ", null, salary.getHospitalId(), SystemConfig.OM_SYSTEM_TYPE, SystemConfig.GROUP_EMPLOYEE);
		
		for(MedicalStaffBasic nuclearE : nuclearEmployeelist) {
			MedicalStaffSalary bean = new MedicalStaffSalary();
			bean.setCalcStime(startTime);
			bean.setCalcEtime(endTime);
			bean.setStaffBasic(nuclearE);
			
			// 已回款订单数量(成单量)
			int orderNum = this.iBaseDao.getIntCount("select count(*) from MedicalOrdersStorage m where m.checkUserId = ?"
					+ " and m.financialStatus = ? and m.createTime between ? and ? ", nuclearE.getUser().getId(), SystemConfig.HAS_SECTIONED, startTime, endTime);
			
			// 绩效薪资
			MedicalPerformanceSalary performanceSalary = (MedicalPerformanceSalary) this.iBaseDao.getObjectByHql("from MedicalPerformanceSalary m "
					+ " where m.deptType = ? and m.isLeader = ? ", nuclearE.getDeptType(), nuclearE.getIsLeader());
			
			// 核单员绩效薪资(每个订单的提成*成单量)
			double amount = (performanceSalary.getMoney().doubleValue()/performanceSalary.getNumber()) * orderNum;
			bean.setEffectSalary(new BigDecimal(amount));	
			
			int count = this.iBaseDao.getIntCount("select count(*) from MedicalLeaveApprove m where m.user.id = ? and m.createTime between ? and ? "
					, nuclearE.getUser().getId(), startTime, endTime);
			BigDecimal leave = new BigDecimal(0);
			if(count > 0){
				List<MedicalLeaveApprove> appList = this.iBaseDao.getBySql("from MedicalLeaveApprove m where m.user.id = ? and m.createTime between ? and ? ", 
						null, nuclearE.getUser().getId(), startTime, endTime);
				for(MedicalLeaveApprove app : appList) {
					MedicalLeaveDeduct deduct = (MedicalLeaveDeduct) this.iBaseDao.getObjectByHql("from MedicalLeaveDeduct m where m.leaveType = ? ", app.getLeaveType());
					if(null != deduct) {
						leave = leave.add(deduct.getDeduct().multiply(new BigDecimal(app.getDays())));
					}
				}
			}
			bean.setLeaveDeduct(leave);
			// 实发工资
			BigDecimal practicalSalary = new BigDecimal(0);	
			// 计算核单员薪资(工资 = 基本工资+提成工资-其他扣款工资)
			practicalSalary = practicalSalary.add(bean.getStaffBasic().getBasic()).add(bean.getEffectSalary()).subtract(leave);
			bean.setPracticalSalary(practicalSalary);
			bean.setHospitalId(salary.getHospitalId());
			Set<ConstraintViolation<Object>> errorInfo = ValidatorUtil.validateObj(bean);
			if(errorInfo.size() > 0) {
				return false;
			} else {
				this.iBaseDao.saveObject(bean);
			}
		}
		
		// 核单组长
		List<MedicalStaffBasic> nuclearGrouplist = this.iBaseDao.getBySql("from MedicalStaffBasic m where m.user.enterpriseId = ? "
				+ "and m.deptType = ? and m.isLeader = ? ", null, salary.getHospitalId(), SystemConfig.OM_SYSTEM_TYPE, SystemConfig.GROUP_LEADER);
		
		for(MedicalStaffBasic nuclearG : nuclearGrouplist) {
			MedicalStaffSalary bean = new MedicalStaffSalary();
			bean.setCalcStime(startTime);
			bean.setCalcEtime(endTime);
			bean.setStaffBasic(nuclearG);
			
			// 查询本核单组完成的总金额
			Object object = this.iBaseDao.getObjectByHql("select sum(m.payment), sum(m.freight) from MedicalOrdersStorage m where m.groupId = ?"
					+ " and m.financialStatus = ? and m.createTime between ? and ? ", nuclearG.getUser().getGroup().getId(), SystemConfig.HAS_SECTIONED, startTime, endTime);
			BigDecimal total = new BigDecimal(0);	// 总金额
			if(null != object) {
				Object[] obj = (Object[]) object;
				if(null != obj[0]) {
					total = total.add(new BigDecimal(obj[0].toString()));
				}
				if(null != obj[1]) {
					total = total.add(new BigDecimal(obj[1].toString()));
				}
			}
			
			// 绩效薪资
			MedicalPerformanceSalary performanceSalary = (MedicalPerformanceSalary) this.iBaseDao.getObjectByHql("from MedicalPerformanceSalary m "
					+ " where m.deptType = ? and m.isLeader = ? ", nuclearG.getDeptType(), nuclearG.getIsLeader());
			
			// 核单组长的绩效薪资(核单组的业绩（总金额）*提成系数（百分比）)
			if(null != performanceSalary) {
				double amount = total.multiply(performanceSalary.getMoney()).doubleValue()/100;
				bean.setEffectSalary(new BigDecimal(amount));	// 核单组长绩效薪资
			} else {
				bean.setEffectSalary(new BigDecimal("0"));
			}
			
			int count = this.iBaseDao.getIntCount("select count(*) from MedicalLeaveApprove m where m.user.id = ? and m.createTime between ? and ? "
					, nuclearG.getUser().getId(), startTime, endTime);
			BigDecimal leave = new BigDecimal(0);
			if(count > 0){
				List<MedicalLeaveApprove> appList = this.iBaseDao.getBySql("from MedicalLeaveApprove m where m.user.id = ? and m.createTime between ? and ? ", 
						null, nuclearG.getUser().getId(), startTime, endTime);
				for(MedicalLeaveApprove app : appList) {
					MedicalLeaveDeduct deduct = (MedicalLeaveDeduct) this.iBaseDao.getObjectByHql("from MedicalLeaveDeduct m where m.leaveType = ? ", app.getLeaveType());
					if(null != deduct) {
						leave = leave.add(deduct.getDeduct().multiply(new BigDecimal(app.getDays())));
					}
				}
			}
			bean.setLeaveDeduct(leave);
			// 实发工资
			BigDecimal practicalSalary = new BigDecimal(0);	
			// 计算核单组长薪资(工资 = 基本工资+提成工资-其他扣款工资)
			if(null != bean.getEffectSalary()) {
				practicalSalary = practicalSalary.add(bean.getStaffBasic().getBasic()).add(bean.getEffectSalary()).subtract(leave);
				bean.setPracticalSalary(practicalSalary);
			} else {
				practicalSalary = practicalSalary.add(bean.getStaffBasic().getBasic()).subtract(leave);
				bean.setPracticalSalary(practicalSalary);
			}
			bean.setHospitalId(salary.getHospitalId());
			Set<ConstraintViolation<Object>> errorInfo = ValidatorUtil.validateObj(bean);
			if(errorInfo.size() > 0) {
				return false;
			} else {
				this.iBaseDao.saveObject(bean);
			}
		}
		return true;
	}
	
	@Override
	public Workbook exportSelected(MedicalPerformanceSalary salary) throws Exception {
		// Excel模版对象
		String AbsolutelyPath = ServletActionContext.getServletContext().getRealPath(SystemConfig.EXPLOYEE_SALARY_INFO);
		File file = new File(AbsolutelyPath);
		if(!file.exists()){
			throw new FileNotFoundException("没有找到excel文件");
		}
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();
		Sheet sheet = workbook.getSheetAt(0);
		int i=0;
		if(StringUtils.isNotBlank(salary.getId())){
			String [] ids =  salary.getId().split(",");
			for(int j=0;j<ids.length;j++){
				// 获得记录
				MedicalStaffSalary bean = (MedicalStaffSalary)this.iBaseDao.getObject(MedicalStaffSalary.class, ids[j]);
				if(null != bean){
					Row currentRow = sheet.createRow(++i);
					currentRow.createCell(0).setCellValue(i);
					MedicalKeyValue keyVal = this.iBaseDao.getValueByKey(bean.getStaffBasic().getUser().getEnterpriseId(), SystemConfig.HM_SYSTEM_TYPE, bean.getStaffBasic().getDeptType());
					if(null != keyVal) {
						currentRow.createCell(1).setCellValue(keyVal.getKeyValue());
					}
					MedicalAdmin user = bean.getStaffBasic().getUser();
					Hibernate.initialize(user.getGroup());
					if(null != user.getGroup()) {
						currentRow.createCell(2).setCellValue(user.getGroup().getPlatform().getName());
						currentRow.createCell(3).setCellValue(user.getGroup().getName());
						currentRow.createCell(4).setCellValue(user.getName());
					}
					if(SystemConfig.GROUP_EMPLOYEE.equals(bean.getStaffBasic().getIsLeader())) {
						currentRow.createCell(5).setCellValue("组员");
					} else if(SystemConfig.GROUP_LEADER.equals(bean.getStaffBasic().getIsLeader())) {
						currentRow.createCell(5).setCellValue("组长");
					} else {}
					if(null != bean.getStaffBasic().getBasic()) {
						currentRow.createCell(6).setCellValue(bean.getStaffBasic().getBasic().toString());
					}
					if(null != bean.getEffectSalary()) {
						currentRow.createCell(7).setCellValue(bean.getEffectSalary().toString());
					}
					if(null != bean.getLeaveDeduct()) {
						currentRow.createCell(8).setCellValue(bean.getLeaveDeduct().toString());
					}
					if(null != bean.getPracticalSalary()) {
						currentRow.createCell(9).setCellValue(bean.getPracticalSalary().toString());
					}
				}
			}
		}
		return workbook;
	}

	@SuppressWarnings("unchecked")
	@Override
	public Workbook exportAll(MedicalPerformanceSalary salary) throws Exception {
		List<MedicalStaffSalary> list = new ArrayList<MedicalStaffSalary>();
		
		Date startTime = null;
		Date endTime = null;
		if(null != salary.getStartTime() && null != salary.getEndTime()) {
			startTime = salary.getStartTime();
			endTime = salary.getEndTime();
		} else {
			startTime = DateUtil.getDate(-31);
			endTime = new Date();
		}
		list = this.iBaseDao.getBySql("from MedicalStaffSalary m where 1=1 and m.hospitalId = ? and m.calcStime >= ? and m.calcEtime <= ? ", null, 
				salary.getHospitalId(), startTime, endTime);
		
		// Excel模版对象
		String AbsolutelyPath = ServletActionContext.getServletContext().getRealPath(SystemConfig.EXPLOYEE_SALARY_INFO);
		File file = new File(AbsolutelyPath);
		if(!file.exists()){
			throw new FileNotFoundException("没有找到excel文件");
		}
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();
		Sheet sheet = workbook.getSheetAt(0);
		int i=0;
		for(MedicalStaffSalary bean : list){
			if(null != bean){
				Row currentRow = sheet.createRow(++i);
				currentRow.createCell(0).setCellValue(i);
				MedicalKeyValue keyVal = this.iBaseDao.getValueByKey(bean.getStaffBasic().getUser().getEnterpriseId(), SystemConfig.HM_SYSTEM_TYPE, bean.getStaffBasic().getDeptType());
				if(null != keyVal) {
					currentRow.createCell(1).setCellValue(keyVal.getKeyValue());
				}
				MedicalAdmin user = bean.getStaffBasic().getUser();
				Hibernate.initialize(user.getGroup());
				if(null != user.getGroup()) {
					currentRow.createCell(2).setCellValue(user.getGroup().getPlatform().getName());
					currentRow.createCell(3).setCellValue(user.getGroup().getName());
					currentRow.createCell(4).setCellValue(user.getName());
				}
				if(SystemConfig.GROUP_EMPLOYEE.equals(bean.getStaffBasic().getIsLeader())) {
					currentRow.createCell(5).setCellValue("组员");
				} else if(SystemConfig.GROUP_LEADER.equals(bean.getStaffBasic().getIsLeader())) {
					currentRow.createCell(5).setCellValue("组长");
				} else {}
				if(null != bean.getStaffBasic().getBasic()) {
					currentRow.createCell(6).setCellValue(bean.getStaffBasic().getBasic().toString());
				}
				if(null != bean.getEffectSalary()) {
					currentRow.createCell(7).setCellValue(bean.getEffectSalary().toString());
				}
				if(null != bean.getLeaveDeduct()) {
					currentRow.createCell(8).setCellValue(bean.getLeaveDeduct().toString());
				}
				if(null != bean.getPracticalSalary()) {
					currentRow.createCell(9).setCellValue(bean.getPracticalSalary().toString());
				}
			}
		}
		return workbook;
	}

	@Override
	public Workbook downloadMedicine(MedicalAdmin admin) throws Exception {
		List<MedicalMedicine> list = this.findMedicineSortList(admin);
		// Excel模版对象
		String AbsolutelyPath = ServletActionContext.getServletContext().getRealPath(SystemConfig.CALL_EXPLOYEE_SALARY);
		File file = new File(AbsolutelyPath);
		if(!file.exists()){
			throw new FileNotFoundException("没有找到excel文件");
		}
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();
		Sheet sheet = workbook.getSheetAt(0);
		int i=0;
		for(MedicalMedicine bean : list){
			if(null != bean){
				Row currentRow = sheet.createRow(++i);
				currentRow.createCell(0).setCellValue(i);
				currentRow.createCell(1).setCellValue(bean.getName());
			}
		}
		return workbook;
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public void importCallSalary(MedicalAdmin admin, File file)
			throws Exception {
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();
		Sheet sheet = workbook.getSheetAt(0);
		Iterator<Row> rows = sheet.rowIterator();
		Row row;
		// 循环导入
		this.iBaseDao.updateHql("delete from MedicalPerformanceSalary m where m.eid = ? and m.deptType = ? and m.isLeader = ? ", admin.getEnterpriseId(), SystemConfig.CM_SYSTEM_TYPE, SystemConfig.GROUP_EMPLOYEE);
		while(rows.hasNext()){
			row = rows.next();
			if(row.getRowNum() == 0) continue;
			if(null == row.getCell(0) || "".equals(row.getCell(0))) continue;
			if(null != getValue(row.getCell(1))){
				MedicalMedicine bean = (MedicalMedicine) this.iBaseDao.getObjectByHql("from MedicalMedicine m where m.name = ? and m.hospitalId = ? ", getValue(row.getCell(1)), admin.getEnterpriseId());
				if(null == bean) continue;
				MedicalPerformanceSalary salary = new MedicalPerformanceSalary();
				salary.setDeptType(SystemConfig.CM_SYSTEM_TYPE);
				salary.setMedicineId(bean.getId());
				if(null != row.getCell(2)) {
					salary.setNumber(Integer.parseInt(getValue(row.getCell(2))));
				}
				if(null != row.getCell(3) && !"".equals(row.getCell(3))) {
					salary.setMoney(new BigDecimal(getValue(row.getCell(3))));
				}
				salary.setIsLeader(SystemConfig.GROUP_EMPLOYEE);
				salary.setEid(admin.getEnterpriseId());
				this.iBaseDao.saveObject(salary);
			}
		}
	}
	
	/**
	 * 列值类型的转换
	 * @author dangjh
	 * @param cell
	 * @return
	 * 2014年3月13日 下午5:56:51
	 */
	private static String getValue(Cell cell) {  
        if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {  
            return String.valueOf(cell.getBooleanCellValue());  
        } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
        	String cellValue = String.valueOf(cell.getNumericCellValue());
        	if(cellValue.contains(".")){
        		cellValue = cellValue.substring(0, cellValue.lastIndexOf("."));
        	}
            return cellValue;  
        } else {
        	cell.setCellType(Cell.CELL_TYPE_STRING);
            return String.valueOf(cell.getStringCellValue());  
        }  
    }
	
}
